﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class JEManager:ConnectionManager
    {
        public void AddJournalEntry(JEUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO OJE (DocNum, Payee, Remarks, DocStatus, DocDate, DateCreated, CreatedBy, DateModified, ModifiedBy) VALUES (@DocNum, @Payee, @Remarks, @DocStatus, @DocDate, @DateCreated, @CreatedBy, @DateModified, @ModifiedBy)";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@Payee", oUnit.Payee));
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.PostingDate));

            oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
            oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
            oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "INSERT INTO JE1 (DocNum, Particulars, AcctCode, AcctName, Debit, Credit) VALUES (@DocNum, @Particulars, @AcctCode, @AcctName, @Debit, @Credit)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));
                if (oRow["Debit"] == DBNull.Value)
                {
                    double _ZeroValue = 0;
                    oCommand.Parameters.Add(new SqlParameter("@Debit", _ZeroValue));
                }
                else
                {

                    string sDebit = oRow["Debit"].ToString();

                    if (sDebit.Trim() != "")
                    {
                        oCommand.Parameters.Add(new SqlParameter("@Debit", Convert.ToDouble(oRow["Debit"])));
                    }
                    else
                    {
                        double _DebitZero = 0;
                        oCommand.Parameters.Add(new SqlParameter("@Debit", _DebitZero));
                    }
                }

                if (oRow["Credit"] == DBNull.Value)
                {
                    double _ZeroValue = 0;
                    oCommand.Parameters.Add(new SqlParameter("@Credit", _ZeroValue));
                }
                else
                {
                    string sCredit = oRow["Credit"].ToString();

                    if (sCredit.Trim() != "")
                    {
                        oCommand.Parameters.Add(new SqlParameter("@Credit", Convert.ToDouble(oRow["Credit"])));
                    }
                    else
                    {
                        double _ZeroValue = 0;
                        oCommand.Parameters.Add(new SqlParameter("@Credit", _ZeroValue));
                    }
                }


                oCommand.ExecuteNonQuery();
            }

        }

        public void UpdateJournalEntry(JEUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OJE SET Payee=@Payee, Remarks=@Remarks, DocStatus=@DocStatus, DocDate=@DocDate, DateModified=@DateModified, ModifiedBy=@ModifiedBy where DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.Parameters.Add(new SqlParameter("@Payee", oUnit.Payee));
            
            oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
            
            oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.PostingDate));

            oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
            oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE FROM JE1 WHERE DocNum=@DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;

                    oCommand.CommandText = "INSERT INTO JE1 (DocNum, Particulars, AcctCode, AcctName, Debit, Credit) VALUES (@DocNum, @Particulars, @AcctCode, @AcctName, @Debit, @Credit)";

                    oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                    oCommand.Parameters.Add(new SqlParameter("@Particulars", oRow["Particulars"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@AcctCode", oRow["AcctCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@AcctName", oRow["AcctName"].ToString()));

                    if (oRow["Debit"].ToString() != "")
                    {
                        oCommand.Parameters.Add(new SqlParameter("@Debit", oRow["Debit"].ToString()));
                    }
                    else
                    {
                        string ZeroValue = "0.00";

                        oCommand.Parameters.Add(new SqlParameter("@Debit", ZeroValue));
                    }

                    if (oRow["Credit"].ToString().Trim() != "")
                    {

                        oCommand.Parameters.Add(new SqlParameter("@Credit", oRow["Credit"].ToString()));
                    }
                    else
                    {
                        string ZeroValue = "0.00";
                        oCommand.Parameters.Add(new SqlParameter("@Credit", ZeroValue));
                    }

                    oCommand.ExecuteNonQuery();
                }
            }
        }

        public void CancelDocument(string _DocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OJE SET DocStatus='Canceled' WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.ExecuteNonQuery();
        }

        public void PostDocument(string _DocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OJE SET DocStatus='Posted' WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.ExecuteNonQuery();
        }

        public void CloseDocument(string _DocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;

            oCommand.CommandText = "UPDATE OJE SET DocStatus='Closed' WHERE DocNum=@DocNum";

            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));
            oCommand.ExecuteNonQuery();
        }

        public DataTable BrowseJEByDocumentNumber(string _DocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _DocNumber = '%' + _DocNumber.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OJE WHERE DocNum LIKE @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));

            oAdapter.SelectCommand = oCommand;
            DataTable dt = new DataTable();

            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseJEByPayee(string _Payee)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _Payee = '%' + _Payee.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OJE WHERE Payee LIKE @Payee";
            oCommand.Parameters.Add(new SqlParameter("@CardName", _Payee));

            oAdapter.SelectCommand = oCommand;
            DataTable dt = new DataTable();

            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable BrowseJEByRemarks(string _Remarks)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            _Remarks = '%' + _Remarks.Trim() + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OJE WHERE Remarks LIKE @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _Remarks));

            oAdapter.SelectCommand = oCommand;
            DataTable dt = new DataTable();

            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetJournalEntry(string _DocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OJE WHERE DocNum = @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));

            oAdapter.SelectCommand = oCommand;
            DataTable dt = new DataTable();

            oAdapter.Fill(dt);

            return dt;
        }

        public DataTable GetJELineItems(string _DocNumber)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM JE1 WHERE DocNum = @DocNum";
            oCommand.Parameters.Add(new SqlParameter("@DocNum", _DocNumber));

            oAdapter.SelectCommand = oCommand;
            DataTable dt = new DataTable();

            oAdapter.Fill(dt);

            return dt;
        }

    }
}
